// 重新初始化数据库
const mysql = require('mysql');
const fs = require('fs');
const path = require('path');

// 读取数据库配置
const config = require('./config') || {
  database: {
    host: 'localhost',
    user: 'root',
    password: '123456zxy',
    port: 3306
  }
};

// 创建连接池
const pool = mysql.createPool({
  host: config.database.host,
  user: config.database.user,
  password: config.database.password,
  port: config.database.port,
  database: 'firstproject'
});

// 执行SQL查询
function query(sql, params = []) {
  return new Promise((resolve, reject) => {
    pool.query(sql, params, (error, results) => {
      if (error) {
        reject(error);
      } else {
        resolve(results);
      }
    });
  });
}

async function reinitDatabase() {
  try {
    console.log('开始重新初始化数据库...');
    
    // 1. 先删除所有表，确保干净的环境
    console.log('删除所有表...');
    await query('SET FOREIGN_KEY_CHECKS = 0');
    await query('DROP TABLE IF EXISTS order_items');
    await query('DROP TABLE IF EXISTS orders');
    await query('DROP TABLE IF EXISTS textbooks');
    await query('DROP TABLE IF EXISTS categories');
    await query('DROP TABLE IF EXISTS users');
    await query('DROP TABLE IF EXISTS books');
    await query('SET FOREIGN_KEY_CHECKS = 1');
    
    // 2. 直接执行修改过的init.sql文件内容
    console.log('执行init.sql文件...');
    const sqlContent = fs.readFileSync(path.join(__dirname, 'database', 'init.sql'), 'utf8');
    
    // 分割SQL语句并执行
    const sqlStatements = sqlContent
      .replace(/\/\*[\s\S]*?\*\//g, '') // 移除注释
      .split(';')
      .filter(statement => statement.trim());
    
    for (const statement of sqlStatements) {
      await query(statement);
      console.log('执行SQL成功');
    }
    
    console.log('数据库重新初始化成功！');
    console.log('现在数据库中应该有以下表：');
    console.log('- users');
    console.log('- categories');
    console.log('- textbooks');
    console.log('- orders');
    console.log('\n注意：外键约束已暂时移除，数据库结构已创建完成。');
    
  } catch (error) {
    console.error('数据库初始化失败:', error);
  } finally {
    pool.end();
  }
}

// 运行重新初始化
reinitDatabase();